<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
	<title>Aggregate Functions | ElasticSearch 7.7 权威指南中文版</title>
	<meta name="keywords" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <meta name="description" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <!-- Give IE8 a fighting chance -->
    <!--[if lt IE 9]>
    <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
    <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
	<link rel="stylesheet" type="text/css" href="../static/styles.css" />
	<script>
	var _link = 'sql-functions-aggs.html';
    </script>
</head>
<body>
<div class="main-container">
    <section id="content">
        <div class="content-wrapper">
            <section id="guide" lang="zh_cn">
                <div class="container">
                    <div class="row">
                        <div class="col-xs-12 col-sm-8 col-md-8 guide-section">
                            <div style="color:gray; word-break: break-all; font-size:12px;">原英文版地址: <a href="https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-functions-aggs.html" rel="nofollow" target="_blank">https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-functions-aggs.html</a>, 原文档版权归 www.elastic.co 所有<br/>本地英文版地址: <a href="../en/sql-functions-aggs.html" rel="nofollow" target="_blank">../en/sql-functions-aggs.html</a></div>
                        <!-- start body -->
                  <div class="page_header">
<strong>重要</strong>: 此版本不会发布额外的bug修复或文档更新。最新信息请参考 <a href="https://www.elastic.co/guide/en/elasticsearch/reference/current/index.html" rel="nofollow">当前版本文档</a>。
</div>
<div id="content">
<div class="breadcrumbs">
<span class="breadcrumb-link"><a href="index.html">Elasticsearch Guide [7.7]</a></span>
»
<span class="breadcrumb-link"><a href="xpack-sql.html">SQL access</a></span>
»
<span class="breadcrumb-link"><a href="sql-functions.html">Functions and Operators</a></span>
»
<span class="breadcrumb-node">Aggregate Functions</span>
</div>
<div class="navheader">
<span class="prev">
<a href="sql-like-rlike-operators.html">« LIKE and RLIKE Operators</a>
</span>
<span class="next">
<a href="sql-functions-grouping.html">Grouping Functions »</a>
</span>
</div>
<div class="section xpack">
<div class="titlepage"><div><div>
<h2 class="title">
<a id="sql-functions-aggs"></a>Aggregate Functions<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a><a class="xpack_tag" href="https://www.elastic.co/subscriptions"></a>
</h2>
</div></div></div>
<p>Functions for computing a <em>single</em> result from a set of input values.
Elasticsearch SQL supports aggregate functions only alongside <a class="xref" href="sql-syntax-select.html#sql-syntax-group-by" title="GROUP BY">grouping</a> (implicit or explicit).</p>
<h3>
<a id="sql-functions-aggs-general"></a>General Purpose<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-aggs-avg"></a><code class="literal">AVG</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">AVG(numeric_field) <a id="CO74-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO74-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>numeric field</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: <code class="literal">double</code> numeric value</p>
<p><span class="strong strong"><strong>Description</strong></span>: Returns the <a href="https://en.wikipedia.org/wiki/Arithmetic_mean" class="ulink" target="_top">Average</a> (arithmetic mean) of input values.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT AVG(salary) AS avg FROM emp;

      avg:d
---------------
48248.55</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-aggs-count"></a><code class="literal">COUNT</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">COUNT(expression) <a id="CO75-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO75-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>a field name, wildcard (<code class="literal">*</code>) or any numeric value</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: numeric value</p>
<p><span class="strong strong"><strong>Description</strong></span>: Returns the total number (count) of input values.</p>
<p>In case of <code class="literal">COUNT(*)</code> or <code class="literal">COUNT(&lt;literal&gt;)</code>, <em>all</em> values are considered (including <code class="literal">null</code> or missing ones).</p>
<p>In case of <code class="literal">COUNT(&lt;field_name&gt;)</code> <code class="literal">null</code> values are not considered.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT COUNT(*) AS count FROM emp;

     count
---------------
100</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-aggs-count-all"></a><code class="literal">COUNT(ALL)</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">COUNT(ALL field_name) <a id="CO76-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO76-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>a field name</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: numeric value</p>
<p><span class="strong strong"><strong>Description</strong></span>: Returns the total number (count) of all <em>non-null</em> input values. <code class="literal">COUNT(&lt;field_name&gt;)</code> and <code class="literal">COUNT(ALL &lt;field_name&gt;)</code> are equivalent.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT COUNT(ALL last_name) AS count_all, COUNT(DISTINCT last_name) count_distinct FROM emp;

   count_all   |  count_distinct
---------------+------------------
100            |96</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-aggs-count-distinct"></a><code class="literal">COUNT(DISTINCT)</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">COUNT(DISTINCT field_name) <a id="CO77-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO77-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>a field name</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: numeric value</p>
<p><span class="strong strong"><strong>Description</strong></span>: Returns the total number of <em>distinct non-null</em> values in input values.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT COUNT(DISTINCT hire_date) unique_hires, COUNT(hire_date) AS hires FROM emp;

  unique_hires  |     hires
----------------+---------------
99              |100</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-aggs-first"></a><code class="literal">FIRST/FIRST_VALUE</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">FIRST(
    field_name               <a id="CO78-1"></a><i class="conum" data-value="1"></i>
    [, ordering_field_name]) <a id="CO78-2"></a><i class="conum" data-value="2"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO78-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>target field for the aggregation</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO78-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>optional field used for ordering</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: same type as the input</p>
<p><span class="strong strong"><strong>Description</strong></span>: Returns the first <span class="strong strong"><strong>non-NULL</strong></span> value (if such exists) of the <code class="literal">field_name</code> input column sorted by
the <code class="literal">ordering_field_name</code> column. If <code class="literal">ordering_field_name</code> is not provided, only the <code class="literal">field_name</code>
column is used for the sorting. E.g.:</p>
<div class="informaltable">
<table border="1" cellpadding="4px">
<colgroup>
<col class="col_1">
<col class="col_2">
</colgroup>
<thead>
<tr>
<th align="left" valign="top">a</th>
<th align="left" valign="top">b</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left" valign="top"><p>100</p></td>
<td align="left" valign="top"><p>1</p></td>
</tr>
<tr>
<td align="left" valign="top"><p>200</p></td>
<td align="left" valign="top"><p>1</p></td>
</tr>
<tr>
<td align="left" valign="top"><p>1</p></td>
<td align="left" valign="top"><p>2</p></td>
</tr>
<tr>
<td align="left" valign="top"><p>2</p></td>
<td align="left" valign="top"><p>2</p></td>
</tr>
<tr>
<td align="left" valign="top"><p>10</p></td>
<td align="left" valign="top"><p>null</p></td>
</tr>
<tr>
<td align="left" valign="top"><p>20</p></td>
<td align="left" valign="top"><p>null</p></td>
</tr>
<tr>
<td align="left" valign="top"><p>null</p></td>
<td align="left" valign="top"><p>null</p></td>
</tr>
</tbody>
</table>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT FIRST(a) FROM t</pre>
</div>
<p>will result in:</p>
<div class="informaltable">
<table border="1" cellpadding="4px">
<colgroup>
<col class="col_1">
</colgroup>
<tbody>
<tr>
<td align="left" valign="top"><p><span class="strong strong"><strong>FIRST(a)</strong></span></p></td>
</tr>
<tr>
<td align="left" valign="top"><p>1</p></td>
</tr>
</tbody>
</table>
</div>
<p>and</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT FIRST(a, b) FROM t</pre>
</div>
<p>will result in:</p>
<div class="informaltable">
<table border="1" cellpadding="4px">
<colgroup>
<col class="col_1">
</colgroup>
<tbody>
<tr>
<td align="left" valign="top"><p><span class="strong strong"><strong>FIRST(a, b)</strong></span></p></td>
</tr>
<tr>
<td align="left" valign="top"><p>100</p></td>
</tr>
</tbody>
</table>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT FIRST(first_name) FROM emp;

   FIRST(first_name)
--------------------
Alejandro</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT gender, FIRST(first_name) FROM emp GROUP BY gender ORDER BY gender;

   gender   |   FIRST(first_name)
------------+--------------------
null        |   Berni
F           |   Alejandro
M           |   Amabile</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT FIRST(first_name, birth_date) FROM emp;

   FIRST(first_name, birth_date)
--------------------------------
Remzi</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT gender, FIRST(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender;

    gender    |   FIRST(first_name, birth_date)
--------------+--------------------------------
null          |   Lillian
F             |   Sumant
M             |   Remzi</pre>
</div>
<p><code class="literal">FIRST_VALUE</code> is a name alias and can be used instead of <code class="literal">FIRST</code>, e.g.:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT gender, FIRST_VALUE(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender;

    gender    |   FIRST_VALUE(first_name, birth_date)
--------------+--------------------------------------
null          |   Lillian
F             |   Sumant
M             |   Remzi</pre>
</div>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p><code class="literal">FIRST</code> cannot be used in a HAVING clause.</p>
</div>
</div>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p><code class="literal">FIRST</code> cannot be used with columns of type <a class="xref" href="text.html" title="Text datatype"><code class="literal">text</code></a> unless
the field is also <a class="xref" href="fielddata.html#before-enabling-fielddata" title="Before enabling fielddata">saved as a keyword</a>.</p>
</div>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-aggs-last"></a><code class="literal">LAST/LAST_VALUE</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">LAST(
    field_name               <a id="CO79-1"></a><i class="conum" data-value="1"></i>
    [, ordering_field_name]) <a id="CO79-2"></a><i class="conum" data-value="2"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO79-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>target field for the aggregation</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO79-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>optional field used for ordering</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: same type as the input</p>
<p><span class="strong strong"><strong>Description</strong></span>: It’s the inverse of <a class="xref" href="sql-functions-aggs.html#sql-functions-aggs-first" title="FIRST/FIRST_VALUE"><code class="literal">FIRST/FIRST_VALUE</code></a>. Returns the last <span class="strong strong"><strong>non-NULL</strong></span> value (if such exists) of the
<code class="literal">field_name</code> input column sorted descending by the <code class="literal">ordering_field_name</code> column. If <code class="literal">ordering_field_name</code> is not
provided, only the <code class="literal">field_name</code> column is used for the sorting. E.g.:</p>
<div class="informaltable">
<table border="1" cellpadding="4px">
<colgroup>
<col class="col_1">
<col class="col_2">
</colgroup>
<thead>
<tr>
<th align="left" valign="top">a</th>
<th align="left" valign="top">b</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left" valign="top"><p>10</p></td>
<td align="left" valign="top"><p>1</p></td>
</tr>
<tr>
<td align="left" valign="top"><p>20</p></td>
<td align="left" valign="top"><p>1</p></td>
</tr>
<tr>
<td align="left" valign="top"><p>1</p></td>
<td align="left" valign="top"><p>2</p></td>
</tr>
<tr>
<td align="left" valign="top"><p>2</p></td>
<td align="left" valign="top"><p>2</p></td>
</tr>
<tr>
<td align="left" valign="top"><p>100</p></td>
<td align="left" valign="top"><p>null</p></td>
</tr>
<tr>
<td align="left" valign="top"><p>200</p></td>
<td align="left" valign="top"><p>null</p></td>
</tr>
<tr>
<td align="left" valign="top"><p>null</p></td>
<td align="left" valign="top"><p>null</p></td>
</tr>
</tbody>
</table>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT LAST(a) FROM t</pre>
</div>
<p>will result in:</p>
<div class="informaltable">
<table border="1" cellpadding="4px">
<colgroup>
<col class="col_1">
</colgroup>
<tbody>
<tr>
<td align="left" valign="top"><p><span class="strong strong"><strong>LAST(a)</strong></span></p></td>
</tr>
<tr>
<td align="left" valign="top"><p>200</p></td>
</tr>
</tbody>
</table>
</div>
<p>and</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT LAST(a, b) FROM t</pre>
</div>
<p>will result in:</p>
<div class="informaltable">
<table border="1" cellpadding="4px">
<colgroup>
<col class="col_1">
</colgroup>
<tbody>
<tr>
<td align="left" valign="top"><p><span class="strong strong"><strong>LAST(a, b)</strong></span></p></td>
</tr>
<tr>
<td align="left" valign="top"><p>2</p></td>
</tr>
</tbody>
</table>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT LAST(first_name) FROM emp;

   LAST(first_name)
-------------------
Zvonko</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT gender, LAST(first_name) FROM emp GROUP BY gender ORDER BY gender;

   gender   |   LAST(first_name)
------------+-------------------
null        |   Patricio
F           |   Xinglin
M           |   Zvonko</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT LAST(first_name, birth_date) FROM emp;

   LAST(first_name, birth_date)
-------------------------------
Hilari</pre>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT gender, LAST(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender;

   gender  |   LAST(first_name, birth_date)
-----------+-------------------------------
null       |   Eberhardt
F          |   Valdiodio
M          |   Hilari</pre>
</div>
<p><code class="literal">LAST_VALUE</code> is a name alias and can be used instead of <code class="literal">LAST</code>, e.g.:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT gender, LAST_VALUE(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender;

   gender  |   LAST_VALUE(first_name, birth_date)
-----------+-------------------------------------
null       |   Eberhardt
F          |   Valdiodio
M          |   Hilari</pre>
</div>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p><code class="literal">LAST</code> cannot be used in <code class="literal">HAVING</code> clause.</p>
</div>
</div>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p><code class="literal">LAST</code> cannot be used with columns of type <a class="xref" href="text.html" title="Text datatype"><code class="literal">text</code></a> unless
the field is also <a class="xref" href="fielddata.html#before-enabling-fielddata" title="Before enabling fielddata"><code class="literal">saved as a keyword</code></a>.</p>
</div>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-aggs-max"></a><code class="literal">MAX</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">MAX(field_name) <a id="CO80-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO80-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>a numeric field</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: same type as the input</p>
<p><span class="strong strong"><strong>Description</strong></span>: Returns the maximum value across input values in the field <code class="literal">field_name</code>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT MAX(salary) AS max FROM emp;

      max
---------------
74999</pre>
</div>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p><code class="literal">MAX</code> on a field of type <a class="xref" href="text.html" title="Text datatype"><code class="literal">text</code></a> or <a class="xref" href="keyword.html" title="Keyword datatype"><code class="literal">keyword</code></a> is translated into
<a class="xref" href="sql-functions-aggs.html#sql-functions-aggs-last" title="LAST/LAST_VALUE"><code class="literal">LAST/LAST_VALUE</code></a> and therefore, it cannot be used in <code class="literal">HAVING</code> clause.</p>
</div>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-aggs-min"></a><code class="literal">MIN</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">MIN(field_name) <a id="CO81-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO81-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>a numeric field</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: same type as the input</p>
<p><span class="strong strong"><strong>Description</strong></span>: Returns the minimum value across input values in the field <code class="literal">field_name</code>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT MIN(salary) AS min FROM emp;

      min
---------------
25324</pre>
</div>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p><code class="literal">MIN</code> on a field of type <a class="xref" href="text.html" title="Text datatype"><code class="literal">text</code></a> or <a class="xref" href="keyword.html" title="Keyword datatype"><code class="literal">keyword</code></a> is translated into
<a class="xref" href="sql-functions-aggs.html#sql-functions-aggs-first" title="FIRST/FIRST_VALUE"><code class="literal">FIRST/FIRST_VALUE</code></a> and therefore, it cannot be used in <code class="literal">HAVING</code> clause.</p>
</div>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-aggs-sum"></a><code class="literal">SUM</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SUM(field_name) <a id="CO82-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO82-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>a numeric field</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: <code class="literal">bigint</code> for integer input, <code class="literal">double</code> for floating points</p>
<p><span class="strong strong"><strong>Description</strong></span>: Returns the sum of input values in the field <code class="literal">field_name</code>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT SUM(salary) AS sum FROM emp;

      sum
---------------
4824855</pre>
</div>
<h3>
<a id="sql-functions-aggs-statistics"></a>Statistics<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-aggs-kurtosis"></a><code class="literal">KURTOSIS</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">KURTOSIS(field_name) <a id="CO83-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO83-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>a numeric field</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: <code class="literal">double</code> numeric value</p>
<p><span class="strong strong"><strong>Description</strong></span>:</p>
<p><a href="https://en.wikipedia.org/wiki/Kurtosis" class="ulink" target="_top">Quantify</a> the shape of the distribution of input values in the field <code class="literal">field_name</code>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT MIN(salary) AS min, MAX(salary) AS max, KURTOSIS(salary) AS k FROM emp;

      min      |      max      |        k
---------------+---------------+------------------
25324          |74999          |2.0444718929142986</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-aggs-mad"></a><code class="literal">MAD</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">MAD(field_name) <a id="CO84-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO84-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>a numeric field</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: <code class="literal">double</code> numeric value</p>
<p><span class="strong strong"><strong>Description</strong></span>:</p>
<p><a href="https://en.wikipedia.org/wiki/Median_absolute_deviation" class="ulink" target="_top">Measure</a> the variability of the input values in the field <code class="literal">field_name</code>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT MIN(salary) AS min, MAX(salary) AS max, AVG(salary) AS avg, MAD(salary) AS mad FROM emp;

      min      |      max      |      avg      |      mad
---------------+---------------+---------------+---------------
25324          |74999          |48248.55       |10096.5</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-aggs-percentile"></a><code class="literal">PERCENTILE</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">PERCENTILE(
    field_name,  <a id="CO85-1"></a><i class="conum" data-value="1"></i>
    numeric_exp) <a id="CO85-2"></a><i class="conum" data-value="2"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO85-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>a numeric field</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO85-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>a numeric expression (must be a constant and not based on a field)</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: <code class="literal">double</code> numeric value</p>
<p><span class="strong strong"><strong>Description</strong></span>:</p>
<p>Returns the nth <a href="https://en.wikipedia.org/wiki/Percentile" class="ulink" target="_top">percentile</a> (represented by <code class="literal">numeric_exp</code> parameter)
of input values in the field <code class="literal">field_name</code>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT languages, PERCENTILE(salary, 95) AS "95th" FROM emp
       GROUP BY languages;

   languages   |      95th
---------------+-----------------
null           |74999.0
1              |72790.5
2              |71924.70000000001
3              |73638.25
4              |72115.59999999999
5              |61071.7</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-aggs-percentile-rank"></a><code class="literal">PERCENTILE_RANK</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">PERCENTILE_RANK(
    field_name,  <a id="CO86-1"></a><i class="conum" data-value="1"></i>
    numeric_exp) <a id="CO86-2"></a><i class="conum" data-value="2"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO86-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>a numeric field</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO86-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>a numeric expression (must be a constant and not based on a field)</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: <code class="literal">double</code> numeric value</p>
<p><span class="strong strong"><strong>Description</strong></span>:</p>
<p>Returns the nth <a href="https://en.wikipedia.org/wiki/Percentile_rank" class="ulink" target="_top">percentile rank</a> (represented by <code class="literal">numeric_exp</code> parameter)
of input values in the field <code class="literal">field_name</code>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT languages, PERCENTILE_RANK(salary, 65000) AS rank FROM emp GROUP BY languages;

   languages   |      rank
---------------+-----------------
null           |73.65766569962062
1              |73.7291625157734
2              |88.88005607010643
3              |79.43662623295829
4              |85.70446389643493
5              |100.0</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-aggs-skewness"></a><code class="literal">SKEWNESS</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SKEWNESS(field_name) <a id="CO87-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO87-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>a numeric field</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: <code class="literal">double</code> numeric value</p>
<p><span class="strong strong"><strong>Description</strong></span>:</p>
<p><a href="https://en.wikipedia.org/wiki/Skewness" class="ulink" target="_top">Quantify</a> the asymmetric distribution of input values in the field <code class="literal">field_name</code>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT MIN(salary) AS min, MAX(salary) AS max, SKEWNESS(salary) AS s FROM emp;

      min      |      max      |        s
---------------+---------------+------------------
25324          |74999          |0.2707722118423227</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-aggs-stddev-pop"></a><code class="literal">STDDEV_POP</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">STDDEV_POP(field_name) <a id="CO88-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO88-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>a numeric field</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: <code class="literal">double</code> numeric value</p>
<p><span class="strong strong"><strong>Description</strong></span>:</p>
<p>Returns the <a href="https://en.wikipedia.org/wiki/Standard_deviations" class="ulink" target="_top">population standard deviation</a> of input values in the field <code class="literal">field_name</code>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT MIN(salary) AS min, MAX(salary) AS max, STDDEV_POP(salary) AS stddev
       FROM emp;

      min      |      max      |      stddev
---------------+---------------+------------------
25324          |74999          |13765.125502787832</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-aggs-sum-squares"></a><code class="literal">SUM_OF_SQUARES</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SUM_OF_SQUARES(field_name) <a id="CO89-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO89-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>a numeric field</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: <code class="literal">double</code> numeric value</p>
<p><span class="strong strong"><strong>Description</strong></span>:</p>
<p>Returns the sum of squares of input values in the field <code class="literal">field_name</code>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT MIN(salary) AS min, MAX(salary) AS max, SUM_OF_SQUARES(salary) AS sumsq
       FROM emp;

      min      |      max      |     sumsq
---------------+---------------+----------------
25324          |74999          |2.51740125721E11</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-aggs-var-pop"></a><code class="literal">VAR_POP</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/aggs.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">VAR_POP(field_name) <a id="CO90-1"></a><i class="conum" data-value="1"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO90-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>a numeric field</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: <code class="literal">double</code> numeric value</p>
<p><span class="strong strong"><strong>Description</strong></span>:</p>
<p>Returns the <a href="https://en.wikipedia.org/wiki/Variance" class="ulink" target="_top">population variance</a> of input values in the field <code class="literal">field_name</code>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT MIN(salary) AS min, MAX(salary) AS max, VAR_POP(salary) AS varpop FROM emp;

      min      |      max      |     varpop
---------------+---------------+----------------
25324          |74999          |1.894786801075E8</pre>
</div>
</div>

</div>
<div class="navfooter">
<span class="prev">
<a href="sql-like-rlike-operators.html">« LIKE and RLIKE Operators</a>
</span>
<span class="next">
<a href="sql-functions-grouping.html">Grouping Functions »</a>
</span>
</div>
</div>

                  <!-- end body -->
                        </div>
                        <div class="col-xs-12 col-sm-4 col-md-4" id="right_col">
                        
                        </div>
                    </div>
                </div>
            </section>
        </div>
    </section>
</div>
<script src="../static/cn.js"></script>
</body>
</html>